﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using Auto.Data.DBUtility;//请先添加引用
using Auto.Data.BusMan.IDAL;
using Auto.Components;
using Model = Auto.Data.BusMan.Model;
namespace Auto.Data.BusMan.SQLServerDAL
{
    /// <summary>
    ///  Data Access Class BuyOilManage。
    /// </summary>
    public class BuyOilManage : IBuyOilManage
    {
        #region --New--
        /// <sumary> 
        /// Constructed Function 
        /// </sumary> 
        public BuyOilManage()
        {
        }
        /// <summary>
        /// Get Model
        /// </summary>
        /// <param name="objKey">key of Table</param>
        public Model.BuyOilManage GetModel(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT TOP 1 RecID, DocID, BuyDate, Buyer, Cost, TotalCost, RecAcct, RecDate, RecDivID, Note, UserAcct, LastUpdateDate");
            strSql.Append(" FROM Bus_BuyOilManage");
            strSql.Append(" WHERE DocID=@DocID AND Void=0");
            SqlParameter[] parameters = SetParametersValue(objKey);
            Model.BuyOilManage model;
            DataRow dr = DbHelper.GetRecord(strSql.ToString(), parameters);
            if (dr != null)
            {
                model = new Model.BuyOilManage(dr);
                return model;
            }
            else
            {
                return null;
            }
        }
        #endregion --New--
        #region --Operate--
        /// <summary>
        /// Is Exists Or Not
        /// </summary>
        public bool Exists(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(1) FROM Bus_BuyOilManage  ");
            strSql.Append(" WHERE DocID=@DocID AND Void=0");
            SqlParameter[] parameters = SetParametersValue(objKey);
            return DbHelper.Exists(strSql.ToString(), parameters);
        }
        #endregion --Operate--
        #region --SQL--
        /// <summary>
        /// Add One Record
        /// </summary>
        public void Add(Model.BuyOilManage model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Bus_BuyOilManage(");
            strSql.Append("DocID,BuyDate,Buyer,Cost,TotalCost,RecAcct,RecDate,RecDivID,Note,UserAcct,LastUpdateDate)");
            strSql.Append(" VALUES (");
            strSql.Append("@DocID,@BuyDate,@Buyer,@Cost,@TotalCost,@RecAcct,@RecDate,@RecDivID,@Note,@UserAcct,@LastUpdateDate)");
            strSql.Append(";UPDATE Bus_BuyOilManage SET DocID=RecID WHERE RecID=@@IDENTITY");
            SqlParameter[] parameters = SetParametersValue(model);
            try
            {
                object obj = DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
                if (obj == null)
                {
                    throw new Exception("Error When Insert Data Into BuyOilManage.");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Update One Record
        /// </summary>
        public void Update(Model.BuyOilManage model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Bus_BuyOilManage SET ");
            strSql.Append("BuyDate=@BuyDate,");
            strSql.Append("Buyer=@Buyer,");
            strSql.Append("Cost=@Cost,");
            strSql.Append("TotalCost=@TotalCost,");
            strSql.Append("RecAcct=@RecAcct,");
            strSql.Append("RecDate=@RecDate,");
            strSql.Append("RecDivID=@RecDivID,");
            strSql.Append("Note=@Note,");
            strSql.Append("UserAcct=@UserAcct,");
            strSql.Append("LastUpdateDate=@LastUpdateDate");
            strSql.Append(" WHERE DocID=@DocID ");
            SqlParameter[] parameters = SetParametersValue(model);
            DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
        /// <summary>
        /// Delete One Record
        /// </summary>
        public void Delete(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE Bus_BuyOilManage ");
            strSql.Append(" WHERE DocID=@DocID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
            //Void(objKey);
        }

        //private void Void(params object[] objKey)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("UPDATE Bus_BuyOilManage SET ");
        //    strSql.Append("Void=1,");
        //    strSql.Append("VoidAcct=@UserAcct,");
        //    strSql.Append("VoidDate=@LastUpdateDate");
        //    strSql.Append("UserAcct=@UserAcct,");
        //    strSql.Append("LastUpdateDate=@LastUpdateDate");
        //    strSql.Append(" WHERE DocID=@DocID");
        //    SqlParameter[] parameters = SetParametersValue(objKey);
        //    DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        //}


        #endregion --SQL--
        #region --Get Data--
        /// <sumary> 
        /// Get Data By Primary Key 
        /// </sumary> 
        /// <param name="objKey">Key of Table</param> 
        /// <returns>DataTable</returns> 
        public DataTable GetDataByKeyValue(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT RecID, DocID, BuyDate, Buyer, Cost, TotalCost, RecAcct, RecDate, RecDivID, Note, UserAcct, LastUpdateDate");
            strSql.Append(" FROM Bus_BuyOilManage");
            strSql.Append(" WHERE DocID=@DocID AND Void=0");
            SqlParameter[] parameters = SetParametersValue(objKey);
            return DbHelper.ExecuteQuery(strSql.ToString(), parameters).Tables[0];
        }
        /// <sumary> 
        /// Get Data List 
        /// </sumary> 
        /// <param name="strWhere">strWhere Format:AND Colname='?' AND ColName2='?'... </param> 
        /// <returns>DataTable</returns> 
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  RecID, DocID, BuyDate, Buyer, Cost, TotalCost, RecAcct, RecDate, RecDivID, Note, UserAcct, LastUpdateDate");
            strSql.Append(" FROM Bus_BuyOilManage ");
            strSql.Append(" WHERE Void=0 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            strSql.Append(" ORDER BY BuyDate DESC ");
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }
        /// <sumary> 
        /// Get TOP Rows Record 
        /// </sumary> 
        /// <param name="top">TOP Rows</param> 
        /// <param name="strWhere">strWhere Format:AND ColName1='?' AND ColName2='?' ..</param> 
        /// <param name="fieldOrder">Field Order By. Format: ColName1,ColName2...</param> 
        /// <returns>DataTable:TOP Rows</returns> 
        public DataTable GetList(int top, string strWhere, string fieldOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ");
            if (top > 0)
            {
                strSql.Append(" TOP " + top.ToString());
            }
            strSql.Append(" RecID, DocID, BuyDate, Buyer, Cost, TotalCost, RecAcct, RecDate, RecDivID, Note, UserAcct, LastUpdateDate ");
            strSql.Append(" FROM Bus_BuyOilManage ");
            strSql.Append(" WHERE Void=0 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            strSql.Append(" ORDER BY " + fieldOrder);
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }
        /// <sumary> 
        /// Convert Table to Model List<Model.BuyOilManage> 
        /// </sumary> 
        /// <param name="dt">DataTable Of BuyOilManage</param> 
        /// <returns>List<Model.BuyOilManage></returns> 
        public List<Model.BuyOilManage> DataTableToList(DataTable dt)
        {
            List<Model.BuyOilManage> modelList = new List<Model.BuyOilManage>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Model.BuyOilManage model;
                for (int n = 0; n < rowsCount; n++)
                {
                    DataRow dr = dt.Rows[n];
                    model = new Model.BuyOilManage(dr);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// Get Data List By Page
        /// </summary>
        public DataTable GetListByPage(int pageSize, int pageIndex, string strWhere)
        {
            SqlParameter[] parameters = {
		   new SqlParameter("@tblName", SqlDbType.VarChar, 255),
		   new SqlParameter("@fldName", SqlDbType.VarChar, 255),
		   new SqlParameter("@PageSize", SqlDbType.Int),
		   new SqlParameter("@PageIndex", SqlDbType.Int),
		   new SqlParameter("@IsReCount", SqlDbType.Bit),
		   new SqlParameter("@OrderType", SqlDbType.Bit),
		   new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
		   };
            parameters[0].Value = "Bus_BuyOilManage";
            parameters[1].Value = "RecID";
            parameters[2].Value = pageSize;
            parameters[3].Value = pageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;
            return DbHelper.ExecuteQueryProcedure("UP_GetRecordByPage", parameters, "ds").Tables[0];
        }

        /// <summary>
        /// 查询数据清单给报表--rptCompanyOilingList.rpt  公司购油统计表
        /// </summary>
        /// <param name="divID">单位编号</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="keywords">关键字</param>
        /// <returns>返回DT</returns>
        public DataTable GetListWithDesc(string divID, string startDate, string endDate, string keywords)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" SELECT b.*,d.ShortName AS DivName 
                             FROM Bus_BuyOilManage b
                             LEFT JOIN _Division d ON b.RecDivID=d.DivID
                             WHERE Void=0");
            if (divID != "")
            {
                strSql.Append(" AND b.RecDivID = '" + divID + "'");
            }
            if (startDate != "" && endDate != "")
            {
                strSql.Append(" AND b.BuyDate>='" + startDate + "' ");
                strSql.Append(" AND b.BuyDate<='" + endDate + "' ");
            }
            else if (startDate != "")
            {
                strSql.Append(" AND b.BuyDate='" + startDate + "' ");
            }
            else if (endDate != "")
            {
                strSql.Append(" AND b.BuyDate='" + endDate + "' ");
            }
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }



        #endregion --Get Data--
        #region ----PrivateSet--
        /// <sumary> 
        /// Set Parameters Value 
        /// </sumary> 
        /// <param name="model">Model</param> 
        /// <returns>Parameters</returns> 
        private SqlParameter[] SetParametersValue(Model.BuyOilManage model)
        {
            #region --Init Data--
            if (model.RecDate == DateTime.MinValue)
            {
                model.RecDate = DateTime.Now;
            }
            if (string.IsNullOrEmpty(model.RecAcct))
            {
                model.RecAcct = model.UserAcct;
            }

            model.LastUpdateDate = DateTime.Now;
            #endregion --Init Data--
            SqlParameter[] parameters = { 
                                    		StrHelper.MakeParam("@DocID",SqlDbType.VarChar,20,ParameterDirection.Input,model.DocID),
                                    		StrHelper.MakeParam("@BuyDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.BuyDate),
                                    		StrHelper.MakeParam("@Buyer",SqlDbType.NVarChar,20,ParameterDirection.Input,model.Buyer),
                                    		StrHelper.MakeParam("@Cost",SqlDbType.Decimal,0,ParameterDirection.Input,model.Cost),
                                    		StrHelper.MakeParam("@TotalCost",SqlDbType.Decimal,0,ParameterDirection.Input,model.TotalCost),
                                    		StrHelper.MakeParam("@RecAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.RecAcct),
                                    		StrHelper.MakeParam("@RecDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.RecDate),
                                    		StrHelper.MakeParam("@RecDivID",SqlDbType.VarChar,20,ParameterDirection.Input,model.RecDivID),
                                            //StrHelper.MakeParam("@Void",SqlDbType.Bit,0,ParameterDirection.Input,model.Void),
                                            //StrHelper.MakeParam("@VoidAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.VoidAcct),
                                            //StrHelper.MakeParam("@VoidDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.VoidDate),
                                    		StrHelper.MakeParam("@Note",SqlDbType.NVarChar,50,ParameterDirection.Input,model.Note),
                                    		StrHelper.MakeParam("@UserAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.UserAcct),
                                    		StrHelper.MakeParam("@LastUpdateDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.LastUpdateDate),

                                 		};
            return parameters;
        }
        /// <sumary> 
        /// Set Parameter Value of Key 
        /// </sumary> 
        /// <param name="objKey">Key Values</param> 
        /// <returns>Parameters</returns> 
        private SqlParameter[] SetParametersValue(object[] objKey)
        {
            SqlParameter[] parameters = { 
                                    		StrHelper.MakeParam("@DocID",SqlDbType.VarChar,20,ParameterDirection.Input,objKey[0]),

                                 		};
            return parameters;
        }
        #endregion ----PrivateSet--
    }
}
